﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace AdoDotNetDatabaseAccess
{
    class Program
    {
        static void Main(string[] args)
        {
            CallLocalDatabaseWithUsing();
            //CallLocalDatabase();
            Console.ReadKey();
        }

        public static void CallLocalDatabaseWithUsing()
        {
            // Aample connection string:
            // Data Source=.;Initial Catalog=GMS;Persist Security Info=True;Integrated Security=True;MultipleActiveResultSets=True
            var csb = new SqlConnectionStringBuilder();
            csb.DataSource = "."; // localhost
            csb.InitialCatalog = "GMS"; // database
            csb.IntegratedSecurity = true; // use windows credentials
            csb.MultipleActiveResultSets = true; // allow multiple open data readers at the same time

            using (SqlConnection conn = new SqlConnection(csb.ToString())) 
            {
                conn.Open();

                List<string> tables = new List<string>();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES";
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string tableName = reader.GetString(reader.GetOrdinal("TABLE_NAME"));
                            tables.Add(tableName);
                        }
                    }
                }

                foreach (var tableName in tables)
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "select count(*) from [" + tableName + "];";
                        var countTableRecords = (int)cmd.ExecuteScalar();
                        Console.ForegroundColor = ConsoleColor.White;
                        Console.WriteLine("----------------------------------------------------------------------");
                        Console.ForegroundColor = ConsoleColor.Cyan;
                        Console.WriteLine(string.Format("The table [{0}] has {1} records", tableName, countTableRecords));
                        Console.ForegroundColor = ConsoleColor.White;
                        Console.WriteLine("----------------------------------------------------------------------");
                    }

                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = string.Format("select top 10 * from [{0}]", tableName);

                        DataTable dataTable = new DataTable();
                        var adapter = new SqlDataAdapter(cmd);
                        adapter.Fill(dataTable);

                        var sb = new StringBuilder();
                        dataTable.Columns.OfType<DataColumn>().ToList().ForEach(col => sb.Append(sb.Length == 0 ? "" : ",").Append(col.ColumnName));
                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.WriteLine(sb.ToString());
                        Console.ForegroundColor = ConsoleColor.White;
                        Console.WriteLine("----------------------------------------------------------------------");

                        foreach (DataRow row in dataTable.Rows)
                        {
                            sb.Clear();
                            row.ItemArray.ToList().ForEach(cell => sb.Append(sb.Length == 0 ? "" : ",").Append(cell.ToString()));

                            /***** Equivilent to this *****
                            foreach (var item in row.ItemArray)
                            {
                                if (sb.Length > 0) sb.Append(",");
                                sb.Append(item);
                            }
                            *******************************/

                            Console.ForegroundColor = ConsoleColor.Magenta;
                            Console.WriteLine(sb.ToString());
                        }
                    }
                }
            }
        }
    }
}
